﻿using System;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ESRI.ArcGIS.DataSourcesFile;
using ESRI.ArcGIS.Display;
using ESRI.ArcGIS.Editor;
using ESRI.ArcGIS.EditorExt;
using ESRI.ArcGIS.esriSystem;
using ESRI.ArcGIS.Geodatabase;
using ESRI.ArcGIS.Geometry;
using ESRI.ArcGIS.GlobeCore;
using ESRI.ArcGIS.NetworkAnalysis;
using ESRI.ArcGIS.Output;
using ESRI.ArcGIS.SystemUI;
using Excel = Microsoft.Office.Interop.Excel;
using System.Windows.Forms;
using System.Data;
using DIWAGIS.Modules;
using Microsoft.Office;
using DIWAGIS.Class;
using System.Reflection;

namespace DIWAGIS.ApplicationLayer.ThongKeTaiSan
{
    public partial class frmBaoCaoLapDatDuongOng : Form
    {
        string sTableName = generalVariable.database + "." + generalVariable.schema + "." + ClassVariables.wOngPhanPhoi;
        Boolean isLoadComBox = false;
        public frmBaoCaoLapDatDuongOng()
        {
            InitializeComponent();
        }

        private void buttonXuatBaoCao_Click(object sender, EventArgs e)
        {
            IFeatureWorkspace pFWS = DIWAGIS.Modules.generalVariable.ICurrentWorkspace as IFeatureWorkspace;
            ITable pTable;
            
            string sTuNgay = "";
            string sDenNgay = "";
            string strWhere = "";
            dateTimePickerTuNgay.CustomFormat = "yyyy/MM/dd 00:00:00";
            this.dateTimePickerTuNgay.Format = System.Windows.Forms.DateTimePickerFormat.Custom;
            sTuNgay = dateTimePickerTuNgay.Text;

            dateTimePickerDenNgay.CustomFormat = "yyyy/MM/dd 00:00:00";
            this.dateTimePickerDenNgay.Format = System.Windows.Forms.DateTimePickerFormat.Custom;
            sDenNgay = dateTimePickerDenNgay.Text;

            if ((dateTimePickerTuNgay.Text != string.Empty) && (dateTimePickerDenNgay.Text != string.Empty))
            {

                strWhere = "NgayLapDat >= '" + sTuNgay + "'" + " AND NgayLapDat <= '" + sDenNgay + "'"; 
            }

            if (comboBoxLoaiOng.Text != string.Empty)
            {
                if (strWhere.ToString() != string.Empty)
                {
                    //strWhere = strWhere & " AND  (CoDongHo= " & Int16.Parse(cbxCoDongHo.SelectedValue) & ")"
                    strWhere = strWhere + " AND LoaiOngNuoc = " + (comboBoxLoaiOng.SelectedValue);
                }
                else
                {
                    strWhere = "LoaiOngNuoc = " + (comboBoxLoaiOng.SelectedValue);
                }
            }

            if (comboBoxCapOng.Text != string.Empty)
            {
                if (strWhere.ToString() != string.Empty)
                {
                    //strWhere = strWhere & " AND  (CoDongHo= " & Int16.Parse(cbxCoDongHo.SelectedValue) & ")"
                    strWhere = strWhere + " AND CapOng = " + (comboBoxCapOng.SelectedValue);
                }
                else
                {
                    strWhere = "CapOng = " + (comboBoxCapOng.SelectedValue);
                }
            }
            pTable = pFWS.OpenTable(sTableName);
            IQueryFilter pQueryfilter = new QueryFilterClass();
            pQueryfilter.WhereClause = strWhere;// "NgayLapDat >= '" + sTuNgay + "'" + " AND NgayLapDat <= '" + sDenNgay + "'";
            Excel.Application oXL;
            Excel.Workbook oWB;
            Excel.Worksheet oSheet;
            Excel.Range oRange;

            // Start Excel and get Application object. 
            oXL = new Excel.Application();

            // Set some properties 
            oXL.Visible = true;
            oXL.DisplayAlerts = false;

            // Get a new workbook. 
            oWB = oXL.Workbooks.Add(Missing.Value);

            // Get the active sheet 
            oSheet = (Excel.Worksheet)oWB.ActiveSheet;
            oSheet.Name = "Báo cáo Phân phối";

            int rowCount = 1;

            ICursor pCursor = pTable.Search(pQueryfilter, false) as ICursor;
            IRow pRow = (IRow)pCursor.NextRow();



            while (pRow != null)
            {
                rowCount += 1;
                for (int i = 1; i < pTable.Fields.FieldCount; i++)
                {
                    // Add the header the first time through 
                    IField curField = pRow.Fields.get_Field(i - 1);
                    if (rowCount == 2)
                    {
                        oSheet.Cells[1, i] = pTable.Fields.get_Field(i - 1).AliasName;
                    }
                    if (pTable.Fields.get_Field(i - 1).Name!="SHAPE")
                    {
                        oSheet.Cells[rowCount, i] = pRow.get_Value(i - 1);
                    }
                }

                pRow = (IRow)pCursor.NextRow();
            }

            GC.WaitForPendingFinalizers();
            GC.Collect();
            GC.WaitForPendingFinalizers();
            GC.Collect();    

        }

        private void frmBaoCaoLapDatDuongOng_Load(object sender, EventArgs e)
        {
            DIWAGIS.Module.WorkingWithDomain cbx = new DIWAGIS.Module.WorkingWithDomain();
            cbx.loadDataDomainToCombobox(comboBoxLoaiOng, DIWAGIS.Modules.generalVariable.ICurrentWorkspace, "DMLoaiOngNuoc");
            cbx.loadDataDomainToCombobox(comboBoxCapOng, DIWAGIS.Modules.generalVariable.ICurrentWorkspace, "DMCapOng");
            SqlCommand sqlcommand = new SqlCommand();

            sqlcommand.CommandText = "SELECT TENHUYEN,IDHUYEN FROM HUYEN ";
            cbx.loadDataToCombobox(comboBoxQuanHuyen, sqlcommand);
            isLoadComBox = true;
        }

        private void comboBoxQuanHuyen_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (isLoadComBox == true)
            {
                string strWhere = "";
                if (!string.IsNullOrEmpty(comboBoxQuanHuyen.Text))
                {
                    strWhere = comboBoxQuanHuyen.SelectedValue.ToString();
                    SqlCommand sqlcommand = new SqlCommand();
                    DIWAGIS.Module.WorkingWithDomain cbx = new DIWAGIS.Module.WorkingWithDomain();
                    //sqlcommand = new System.Data.SqlClient.SqlCommand();
                    sqlcommand.CommandText = "SELECT TenHanhChinh,IDHanhChinh FROM HANHCHINH WHERE MaHuyen = @MaHuyen";
                    sqlcommand.Parameters.AddWithValue("@MaHuyen", strWhere);
                    cbx.loadDataToCombobox(comboBoxPhuongXa, sqlcommand);
                    //loadDataToCombobox(cbxMaPhuong, sqlcommand);

                }
            }
        }
    }
}
